﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LotteryVote.Entity;
using System.Data.SqlClient;
using LotteryVote.Entity.Commission;

namespace LotteryVote.Data
{
    public class CommissionGroupDataAccess : DataBase
    {
        #region BaseSQL
        readonly string _baseSql = @"select cg.GroupName,cg.SpecieId,cg.CreateTime as CommissionGroup_CreateTime,
cc.*,gt.GameName,gt.Description,gt.CreateTime as GameType_CreateTime,
ct.Id,ct.TypeName,ct.CreateTime as CompanyType_CreateTime 
from tb_CommissionGroup cg
join tb_ConcreteCommission cc on cc.GroupId=cg.GroupId
join tb_GameType gt on gt.GameId=cc.GameId
join tb_CompanyType ct on ct.Id=cc.CompanyTypeId";
        #endregion

        public void InsertCommissionGroup(CommissionGroup group)
        {
            string sql = string.Format(@"INSERT INTO {0} ({1},{2}) VALUES (@{1},@{2}) SELECT IDENT_CURRENT('{0}')",
                CommissionGroup.TABLENAME, CommissionGroup.GROUPNAME, CommissionGroup.SPECIEID);
            object id = base.ExecuteScalar(sql, new SqlParameter(CommissionGroup.GROUPNAME, group.GroupName),
                new SqlParameter(CommissionGroup.SPECIEID, group.SpecieId));
            group.GroupId = Convert.ToInt32(id);
        }
        public void DeleteCommissionGroup(int groupId)
        {
            string sql = string.Format(@"DELETE {0} WHERE {1}=@{1}", CommissionGroup.TABLENAME, CommissionGroup.GROUPID);
            base.ExecuteNonQuery(sql, new SqlParameter(CommissionGroup.GROUPID, groupId));
        }

        public CommissionGroup GetCommissionGroup(int groupId)
        {
            string sql = string.Format(@"SELECT * FROM {0} WHERE {1}=@{1}", CommissionGroup.TABLENAME, CommissionGroup.GROUPID);
            return base.ExecuteEntity<CommissionGroup>(sql, new SqlParameter(CommissionGroup.GROUPID, groupId));
        }
        public CommissionGroup GetCommissionGroup(int specieId, string groupName)
        {
            string sql = string.Format(@"SELECT * FROM {0} WHERE {1}=@{1} AND {2}=@{2}", CommissionGroup.TABLENAME,
                CommissionGroup.SPECIEID, CommissionGroup.GROUPNAME);
            return base.ExecuteEntity<CommissionGroup>(sql, new SqlParameter(CommissionGroup.SPECIEID, specieId),
                new SqlParameter(CommissionGroup.GROUPNAME, groupName));
        }
        public CommissionGroupModel GetCommissionGroupModel(int groupId)
        {
            string sql = string.Format(@"{0} where cg.{1}=@{1}", _baseSql, CommissionGroup.GROUPID);
            var ds = base.ExecuteDataSet(sql, new SqlParameter(CommissionGroup.GROUPID, groupId));
            CommissionGroupModel model = new CommissionGroupModel();
            model.CommGroup = base.ToEntity<CommissionGroup>(ds.Tables[0]);
            //TODO:频繁创建相同内容的对象，性能瓶颈
            model.ConcreteCommissionList = base.ToEnties<ConcreteCommission>(ds.Tables[0]);
            return model;
        }
        public CommissionGroupModel GetCommissionGroupModelByMember(User member, LotterySpeciesEnum speice)
        {
            string sql = string.Format(@"{0} join {1} mp on mp.{2}=cg.{3}
where mp.{4}=@{4} and mp.{2}=@{2} and cg.{5}=mp.{6}", _baseSql, MemberPackage.TABLENAME, MemberPackage.SPECIEID, CommissionGroup.SPECIEID,
                             MemberPackage.USERID, CommissionGroup.GROUPID, MemberPackage.GROUPID);
            var ds = base.ExecuteDataSet(sql, new SqlParameter(MemberPackage.USERID, member.UserId),
                new SqlParameter(MemberPackage.SPECIEID, (int)speice));
            CommissionGroupModel model = new CommissionGroupModel();
            model.CommGroup = base.ToEntity<CommissionGroup>(ds.Tables[0]);
            //TODO:频繁创建相同内容的对象，性能瓶颈
            model.ConcreteCommissionList = base.ToEnties<ConcreteCommission>(ds.Tables[0]);
            return model;
        }
        public IList<CommissionGroupModel> GetCommissionGroupBySpecie(LotterySpecies specie)
        {
            string sql = string.Format(@"{0} where cg.{1}=@{1}", _baseSql, CommissionGroup.SPECIEID); ;
            var ds = base.ExecuteDataSet(sql, new SqlParameter(CommissionGroup.SPECIEID, specie.SpecieId));
            var groups = base.ToEnties<CommissionGroup>(ds.Tables[0]).Distinct(new CommissionGroupEqualityComparer());
            var concreteCommissionList = base.ToEnties<ConcreteCommission>(ds.Tables[0]);
            var concreteCommGroup = concreteCommissionList.GroupBy(it => it.Group.GroupId);
            List<CommissionGroupModel> modelList = new List<CommissionGroupModel>();
            foreach (var group in groups)
            {
                modelList.Add(new CommissionGroupModel
                {
                    CommGroup = group,
                    ConcreteCommissionList = concreteCommGroup.Where(it => it.Key == group.GroupId).FirstOrDefault().ToList<ConcreteCommission>()
                });
            }
            return modelList;
        }
        public IList<CommissionGroup> GetCommissionGroupByGroupId(params int[] groupIdArr)
        {
            if (groupIdArr.Length == 0) return new List<CommissionGroup>();
            string sql = string.Format(@"SELECT * FROM {0} WHERE {1} IN (@{1})", CommissionGroup.TABLENAME, CommissionGroup.GROUPID);
            StringBuilder sb = new StringBuilder();
            bool isFirst = true;
            foreach (int groupId in groupIdArr)
            {
                if (isFirst)
                    isFirst = false;
                else
                    sb.Append(",");
                sb.Append(groupId);
            }
            return base.ExecuteList<CommissionGroup>(sql, new SqlParameter(CommissionGroup.GROUPID, sb.ToString()));
        }
        public IList<CommissionGroup> GetCommissionGroupBySpecie(LotterySpeciesEnum specie)
        {
            string sql = string.Format(@"SELECT * FROM {0} WHERE {1}=@{1}", CommissionGroup.TABLENAME, CommissionGroup.SPECIEID);
            return base.ExecuteList<CommissionGroup>(sql, new SqlParameter(CommissionGroup.SPECIEID, (int)specie));
        }
        public IList<CommissionGroup> GetAll()
        {
            string sql = string.Format(@"SELECT * FROM {0}", CommissionGroup.TABLENAME);
            return base.ExecuteList<CommissionGroup>(sql);
        }
    }
}
